package com.cn.ccp.service.faculty;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;

import com.cn.ccp.bean.FacultyBean;
import com.cn.ccp.service.BasicService;

/**
 * 学院管理
 * @author Administrator
 *
 */
public class FacultyService extends BasicService{
	private Log log = LogFactory.getLog(FacultyService.class);
		
	/**
	 * 创建学院
	 * @param 
	 * @return
	 */
	public boolean saveFaculty(FacultyBean bo){
		log.info(bo);
		String sql = "insert into faculty(facultyname,description) values (?,?)";
		return jdbcTemplate.update(sql ,new Object[]{bo.getFacultyName(),bo.getDescription()}) > 0;
	}
	
	/**
	 * 更新学院
	 * @param bo
	 * @return
	 */
	public boolean updateFaculty(FacultyBean bo){
		log.info(bo);
		String sql = "update faculty set facultyname = ?, description = ? where id = ?";
		return jdbcTemplate.update(sql ,new Object[]{bo.getFacultyName(),bo.getDescription(),bo.getId()}) > 0; 
	}
	
	/**
	 * 删除学院
	 * @param id
	 * @return
	 */
	public boolean deleteFaculty(int id) {
		String sql = "delete from faculty where id = ?";
		try {
			return jdbcTemplate.update(sql ,new Object[]{id}) > 0;
		} catch (Exception e) {
			return false;
		}
	}
	
	/**
	 *根据id查询学院
	 * @param id
	 * @return
	 */
	public FacultyBean findFacultyById(int id) {
		String sql = "SELECT * FROM faculty WHERE id = ?";
		return (FacultyBean) jdbcTemplate.queryForObject(sql, new Object[] { id }, new FacultyMapper());
	}
	
	
	
	@SuppressWarnings("unchecked")
	public List pageQueryFaculty(int start, int pagesize) {
		String sql = "select * from faculty limit ?,?";
		return jdbcTemplate.query(sql, new Object[] { start,pagesize},new FacultyMapper());
	}
	public int getCountFaculty() {
		String sql = "select count(1) from faculty ";
		return jdbcTemplate.queryForInt(sql);
	}
	public int getFacultyIdByfacultyName(String name) {
		String sql = "SELECT id FROM faculty WHERE facultyname = ? ";
		return (Integer) jdbcTemplate.queryForObject(sql, new Object[]{name} , Integer.class);
	}
	
	public boolean facultyNameIsAlready(int id ,String name) {
		String sql = "SELECT * FROM faculty WHERE facultyname = ? and id != ?";
		return jdbcTemplate.query(sql, new Object[] { name, id }, new FacultyMapper()).size() > 0;
	}
	
	public List<FacultyBean> getAllFaculty(){
		String sql = "SELECT f.* , cm.id as cmId FROM faculty f "+
					" LEFT OUTER JOIN  course_mapping cm ON cm.other_id = f.id AND cm.course_mapping_type = 4 group by f.id";
		return jdbcTemplate.query(sql,new FacultyMapper());
	}
	
	public List<FacultyBean> getCourseMapingFaculty(String courseId){
//		String sql = "SELECT f.* , cm.id as cmId FROM faculty f "+
//					" LEFT OUTER JOIN  course_mapping cm ON cm.other_id = f.id AND cm.course_mapping_type = 4 and cm.course_id = ?";
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT   f.* , cm.id as cmId FROM faculty f  ");
		sql.append(" LEFT OUTER JOIN course_mapping cm ON  cm.course_id = ? and ");
		sql.append("((cm.other_id = f.id AND cm.course_mapping_type = 4) ");
		sql.append("or ( cm.other_id in ( select id from specialty where faculty_id = f.id ) AND cm.course_mapping_type = 3)");
		sql.append("or (cm.other_id in (select id from grade_type where specialty_id in ( select id from specialty where faculty_id = f.id )) AND cm.course_mapping_type = 2)	");
		sql.append("or (cm.other_id in (select id from user where grade in (select id from grade_type where specialty_id in ( select id from specialty where faculty_id = f.id ))) AND cm.course_mapping_type = 1)");
		sql.append(") group by f.id");
		
		
		return jdbcTemplate.query(sql.toString(),new Object[]{courseId},new FacultyMapper());
	}
	
	
	
	
	/**
	 * 学院封装类
	 * @author Administrator
	 *
	 */
	public class FacultyMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			FacultyBean bean = new FacultyBean();
			bean.setId(rs.getInt("id"));
			bean.setFacultyName(rs.getString("facultyname"));
			bean.setDescription(rs.getString("description"));
			if(bean.getDescription() != null && !bean.getDescription().equals("") && bean.getDescription().length() > 20)
				bean.setDescription_tailor(bean.getDescription().substring(0,20));	
			else
				bean.setDescription_tailor(bean.getDescription());	
			
			try {
				String cmid = rs.getString("cmId");
				if(cmid != null  && !cmid.equals(""))
					bean.setReleaseSelect(true);
			} catch (Exception e) {
				// TODO: handle exception
			}
			return bean;
		}
	}
	
}
